﻿namespace AnyOffice.DAL
{
    using AnyOffice.Model;
    using Microsoft.ApplicationBlocks.Data;
    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Text;

    public class SysFunctionDao
    {
        public bool Delete(SqlConnection conn, int funcId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("DECLARE @MenuId nvarchar(50) ");
            builder.Append("SELECT @MenuId = [MenuId] FROM [dbo].[Sys_Function] WHERE [FuncId] = @FuncId ");
            builder.Append("IF(LEN(ISNULL(@MenuId, ''))) = 4 ");
            builder.Append("\tDELETE FROM [dbo].[Sys_Function] WHERE LEFT([MenuId],4) = @MenuId ");
            builder.Append("DELETE FROM [dbo].[Sys_Function] WHERE [FuncId] = @FuncId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@FuncId", SqlDbType.Int) };
            commandParameters[0].Value = funcId;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public bool Insert(SqlConnection conn, SysFunctionData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("INSERT INTO [dbo].[Sys_Function]  ");
            builder.Append("( ");
            builder.Append("\t[MenuId], ");
            builder.Append("\t[FuncName], ");
            builder.Append("\t[FuncCode], ");
            builder.Append("\t[FuncImg] ");
            builder.Append(")  ");
            builder.Append("VALUES  ");
            builder.Append("( ");
            builder.Append("\t@MenuId, ");
            builder.Append("\t@FuncName, ");
            builder.Append("\t@FuncCode, ");
            builder.Append("\t@FuncImg ");
            builder.Append(") ");
            SqlParameter[] commandParameters = new SqlParameter[4];
            commandParameters[0] = new SqlParameter("@MenuId", SqlDbType.NVarChar, 50);
            commandParameters[0].Value = data.MenuId;
            commandParameters[1] = new SqlParameter("@FuncName", SqlDbType.NVarChar, 100);
            commandParameters[1].Value = data.FuncName;
            commandParameters[2] = new SqlParameter("@FuncCode", SqlDbType.NVarChar, 100);
            commandParameters[2].Value = data.FuncCode;
            commandParameters[3] = new SqlParameter("@FuncImg", SqlDbType.NVarChar, 50);
            commandParameters[3].Value = data.FuncImg;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }

        public DataTable SelectByFuncCode(SqlConnection conn, string funcCode)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[FuncId], ");
            builder.Append("\t[MenuId], ");
            builder.Append("\t[FuncName], ");
            builder.Append("\t[FuncCode], ");
            builder.Append("\t[FuncImg] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Sys_Function] ");
            builder.Append("WHERE ");
            builder.Append("\t[FuncCode] = @FuncCode ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@FuncCode", SqlDbType.NVarChar, 100) };
            commandParameters[0].Value = funcCode;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Sys_Function";
            return set.Tables[0];
        }

        public DataTable SelectByFuncId(SqlConnection conn, int funcId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[FuncId], ");
            builder.Append("\t[MenuId], ");
            builder.Append("\t[FuncName], ");
            builder.Append("\t[FuncCode], ");
            builder.Append("\t[FuncImg] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Sys_Function] ");
            builder.Append("WHERE ");
            builder.Append("\t[FuncId] = @FuncId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@FuncId", SqlDbType.Int) };
            commandParameters[0].Value = funcId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Sys_Function";
            return set.Tables[0];
        }

        public DataTable SelectByMenuId(SqlConnection conn, string menuId)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("SELECT ");
            builder.Append("\t[FuncId], ");
            builder.Append("\t[MenuId], ");
            builder.Append("\t[FuncName], ");
            builder.Append("\t[FuncCode], ");
            builder.Append("\t[FuncImg] ");
            builder.Append("FROM ");
            builder.Append("\t[dbo].[Sys_Function] ");
            builder.Append("WHERE ");
            builder.Append("\t[MenuId] = @MenuId ");
            SqlParameter[] commandParameters = new SqlParameter[] { new SqlParameter("@MenuId", SqlDbType.NVarChar, 50) };
            commandParameters[0].Value = menuId;
            DataSet set = SqlHelper.ExecuteDataset(conn, CommandType.Text, builder.ToString(), commandParameters);
            set.Tables[0].TableName = "Sys_Function";
            return set.Tables[0];
        }

        public bool Update(SqlConnection conn, SysFunctionData data)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SET NOCOUNT ON ");
            builder.Append("UPDATE [dbo].[Sys_Function] ");
            builder.Append("SET ");
            builder.Append("\t[MenuId] = @MenuId, ");
            builder.Append("\t[FuncName] = @FuncName, ");
            builder.Append("\t[FuncCode] = @FuncCode, ");
            builder.Append("\t[FuncImg] = @FuncImg ");
            builder.Append("WHERE ");
            builder.Append("\t[FuncId] = @FuncId ");
            SqlParameter[] commandParameters = new SqlParameter[5];
            commandParameters[0] = new SqlParameter("@FuncId", SqlDbType.Int);
            commandParameters[0].Value = data.FuncId;
            commandParameters[1] = new SqlParameter("@MenuId", SqlDbType.NVarChar, 50);
            commandParameters[1].Value = data.MenuId;
            commandParameters[2] = new SqlParameter("@FuncName", SqlDbType.NVarChar, 100);
            commandParameters[2].Value = data.FuncName;
            commandParameters[3] = new SqlParameter("@FuncCode", SqlDbType.NVarChar, 100);
            commandParameters[3].Value = data.FuncCode;
            commandParameters[4] = new SqlParameter("@FuncImg", SqlDbType.NVarChar, 50);
            commandParameters[4].Value = data.FuncImg;
            SqlHelper.ExecuteNonQuery(conn, CommandType.Text, builder.ToString(), commandParameters);
            return true;
        }
    }
}

